"""
Case Type   : pg_buffercache_pages验证
Case Name   : pg_buffercache_pages函数，索引对象isvalid字段验证
Create At   : 2022-01-11
Owner       : opentestcase044
Description :
    1、重启数据库后，gsql连接数据库，查询isvalid为f的的缓冲区
    2、gsql连接数据库，创建表及索引
    3、初始查询表的缓存信息
    4、表中插入一定量数据
    5、再次查询表的缓存信息
    6、环境清理
Expect      :
    1、重启数据库后，gsql连接数据库，查询isvalid为f的的缓冲区，为未使用缓冲区，除了isvalid和bufferid字段为，其余为空
    2、gsql连接数据库，创建表及索引成功
    3、初始查询表的索引缓存信息，为空（查询出的缓存记录为0行）;
    4、表中插入一定量数据成功
    5、再次查询表的索引缓存信息，isvalid字段均为t
    6、环境清理成功
History     :
    modified by haomeng at 2024.5.28:优化用例适配资源池化
"""

import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgBuffercachePagesCase0022(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(
            'Opengauss_Function_Innerfunc_Pg_Buffercache_Pages_Case0022:初始化')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.t_name1 = 't_pg_buffercache_pages_case0022_1'
        self.i_name1 = 'i_pg_buffercache_pages_case0022_1'

    def test_main(self):
        step_txt = f'----step1:重启数据库后，查询isvalid为f的的缓冲区，' \
            f'expect: 查询isvalid为f的的缓冲区，为未使用缓冲区，' \
            f'除了isvalid和bufferid字段外，其余为空----'
        self.log.info(step_txt)
        restart_flag1 = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart_flag1, '重启数据库失败')
        select_sql = f'select case when count(*)=0 then null end ' \
            f'from pg_buffercache_pages() where ' \
            f'isvalid=false and bufferid is null;' \
            f'select distinct concat(relfilenode,bucketid,' \
            f'storage_type,reltablespace,reldatabase,relforknumber,' \
            f'relblocknumber,isdirty,usage_count,pinning_backends) ' \
            f'null_value from pg_buffercache_pages() where isvalid=false;'
        self.log.info(select_sql)
        select_result = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(select_result)
        null_value1 = select_result.strip().splitlines()[2].strip()
        null_value2 = select_result.strip().splitlines()[7].strip()
        self.assertEqual(null_value1, '', '执行失败:' + step_txt)
        self.assertEqual(null_value2, '', '执行失败:' + step_txt)

        step_txt = '----step2:创建表和索引，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f'drop table if exists {self.t_name1};' \
            f'create table {self.t_name1}(id int,content text);' \
            f'create index {self.i_name1} on {self.t_name1}(id);'
        self.log.info(create_sql)
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step3: 初始查询表的索引缓存信息，expect: 不为0行----'
        self.log.info(step_txt)
        select_sql = f"select count(*) from pg_buffercache_pages() where " \
            f"relfilenode in (select relfilenode from pg_class " \
            f"where relname='{self.i_name1}');"
        self.log.info(select_sql)
        select_result = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(select_result)
        tmp_count1 = int(select_result.strip().splitlines()[-2])
        self.assertNotEqual(tmp_count1, 0, '执行失败：' + step_txt)

        step_txt = '----step4:表中插入一定量的数据，expect: 插入成功----'
        self.log.info(step_txt)
        insert_sql = f"insert into {self.t_name1} " \
            f"values(generate_series(1, 1000), 'testtext');"
        self.log.info(insert_sql)
        insert_result = self.pri_sh.execut_db_sql(insert_sql)
        self.log.info(insert_result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_result,
                      '执行失败:' + step_txt)

        step_txt = f'----step5: 再次查询索引的缓存信息，expect: isdirty字段为t'
        self.log.info(step_txt)
        select_sql = f"select distinct isvalid from pg_buffercache_pages() " \
            f"where relfilenode in (select relfilenode from pg_class " \
            f"where relname='{self.t_name1}') order by 1 desc;"
        self.log.info(select_sql)
        select_result = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(select_result)
        isdirty_flag = select_result.strip().splitlines()[-2].strip()
        self.assertEqual(isdirty_flag, 't', '执行失败：' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step6: 环境清理，expect: 环境清理成功----'
        self.log.info(step_txt)
        drop_sql = f'drop table if exists {self.t_name1};'
        self.log.info(drop_sql)
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_result,
                      '执行失败：' + step_txt)
        self.log.info(
            'Opengauss_Function_Innerfunc_Pg_Buffercache_Pages_Case0022:执行完毕')
